package weaver.splittable.biz.workflow;

import com.icbc.api.internal.apache.http.impl.cookie.S;
import net.sf.jsqlparser.expression.LongValue;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import weaver.conn.ConnectionPool;
import weaver.conn.WeaverConnection;
import weaver.conn.WeaverStatement;
import weaver.splittable.STConfig;
import weaver.splittable.biz.STBizInterceptor;
import weaver.splittable.db.STDBConstants;
import weaver.splittable.db.STDBSupport;
import weaver.splittable.modal.STInterceptResult;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class STWorkflowInterceptor implements STBizInterceptor {

    private static final Logger LOGGER = Logger.getLogger(STConfig.LOGGER_NAME);

    private static ConnectionPool connectionPool;
    private static Integer REQUEST_ID_THRESHOLD = 0;
    private static String TIME_THRESHOLD = "0000-00-00"; //
    private static List<Integer> ILLEGAL_REQUEST_ID=new ArrayList();

    static {
        connectionPool = ConnectionPool.getInstance();
    }

    @Override
    public void init() {
        // 初始化 requestid 阈值和时间阈值
        updateRequestIdThreshold();
		initThresholdData();
    }

    @Override
    public boolean needIntercept(String sql) {
        sql = sql.trim().toLowerCase();
        for (String tableName: STWorkflowConstants.SPLIT_TABLES.keySet()) {
            tableName = tableName.toLowerCase();
            if (sql.contains(tableName)) {
                if (sql.contains("requestid") || sql.contains("createdate")) {
                    return true;
                }
            }
        }
        return false;
    }

    @Override
    public STInterceptResult intercept(String sql) {

        boolean isReplaced = false;

        Map<String, List<STWorkflowWhereInfo>> wheres = STWorkflowMatchUtil.getWheres(sql);

        if (wheres.size() > 0) {

            // 规则一：根据requestId判断是否需要处理
            // 获取到了全部包含requestId的where条件，只要有一个符合，整个SQL都要替换
            List<STWorkflowWhereInfo> requestIdInfos = wheres.get("requestid");
            if (requestIdInfos != null) {
                for (STWorkflowWhereInfo requestIdInfo: requestIdInfos) {
                    if (isReplaced) {
                        break; // 循环前先判断是否已经替换过SQL了，替换过就不用循环了 直接break
                    }
                    String operator = requestIdInfo.getOperator();
                    if ("=".equals(operator) || "<".equals(operator) || "<=".equals(operator)) {
                        Object requestIdObj = requestIdInfo.getRight();
                        if (requestIdObj instanceof LongValue || requestIdObj instanceof Integer || requestIdObj instanceof Long) {
                            int requestId = Integer.valueOf(requestIdObj.toString());
                            if (ILLEGAL_REQUEST_ID.contains(requestId)){
                                sql = replaceSQL(sql);
                                isReplaced = true;
                            }
                            if (requestId < REQUEST_ID_THRESHOLD && !checkDataExist(requestId) && requestId > 0) {
                                sql = replaceSQL(sql);
                                isReplaced = true;
                            }
                        }
                    } else if ("in".equals(operator)) {
                        List<Integer> inRequestIds = requestIdInfo.getInItems();
                        // 如果in的数量小于20 遍历判断
                        if (inRequestIds.size() < 20) {
                            for (Integer inRequestId: inRequestIds) {
                                if (inRequestId < REQUEST_ID_THRESHOLD && !checkDataExist(inRequestId)) {
                                    sql = replaceSQL(sql);
                                    isReplaced = true;
                                    break;
                                }
                            }
                        } else {
                            // 如果in的数量大于20 取其中最大值做一次范围判断
                            Integer maxRequestId = Collections.max(inRequestIds);
                            if (maxRequestId < REQUEST_ID_THRESHOLD && !checkDataExist(maxRequestId)) {
                                sql = replaceSQL(sql);
                                isReplaced = true;
                            }
                        }
                    }
                }
            }

            // 规则二：根据时间字段判断是否需要处理
            List<STWorkflowWhereInfo> createdateInfos = wheres.get("createdate");
            if (createdateInfos != null && !isReplaced) {
                for (STWorkflowWhereInfo createdateInfo: createdateInfos) {
                    //String operator = createdateInfo.getOperator();
                    String createdate = createdateInfo.getRight().toString();
                    createdate = createdate.replace("'", "");
                    if (checkTableByTime(createdate) == 1) {
                        sql = replaceSQL(sql);
                        isReplaced = true;
                    }
                }
            }
        }

        return new STInterceptResult(isReplaced, sql);
    }

    /**
     * 检查流程数据是否存在
     * @param id 流程requestid
     * @return 是否存在
     */
    private static boolean checkDataExist(Integer id){
        WeaverConnection connection = null;
        WeaverStatement statement = null;
        try {
            connection = connectionPool.getConnection();
            statement = (WeaverStatement) connection.createStatement();
            String checkSql = "select 1 from workflow_requestbase where requestid = " + id;
            ResultSet rs = statement.originExecuteQuery(checkSql);
            while (rs.next()){
                return true;
            }
        } catch (Exception e) {
            LOGGER.error("Error:", e);
        } finally {
            if (statement != null) {
                statement.closeNoException();
            }
            if (connection != null)
                connectionPool.returnConnection(connection);
        }
        return false;
    }

    //通过createTime判断数据属于哪个表，返回int类型，0表示现有表，1表示全量表
    public static Integer checkTableByTime(String createTime){
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            Date timeData = dateFormat.parse(TIME_THRESHOLD);
            Date createTimeDate = dateFormat.parse(createTime);
            if (createTimeDate.getTime()>timeData.getTime()){
                return 0;
            }
        } catch (ParseException e) {
            LOGGER.error("Error:", e);
        }
        return 1;
    }

    /**
     * 更新requestId阈值
     */
    public static void updateRequestIdThreshold(){
        LOGGER.info("===== STWorkflowInterceptor =====");
        LOGGER.info("原REQUESTID阈值：" + REQUEST_ID_THRESHOLD);
        LOGGER.info("原时间阈值：" + TIME_THRESHOLD);
        if (StringUtils.isNotEmpty(STConfig.SPLIT_TIME)) {
             Calendar calendar = Calendar.getInstance();
            DateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd");
            calendar.setTime(new Date());
            calendar.add(Calendar.DAY_OF_MONTH,-Integer.valueOf(STConfig.SPLIT_TIME));
            String dateTime = dFormat.format(calendar.getTime());
            TIME_THRESHOLD = dateTime;

            String sql = "select min(requestid) id from workflow_requestbase";
            String illegalQuarySql="select requestid from workflow_requestbase_all where requestid >(select requestid_threshold from split_data_threshold_info where module ='workflow')  and  createdate < (select createdate_threshold from split_data_threshold_info)";
            WeaverConnection connection = null;
            WeaverStatement statement = null;
            try {
                connection = connectionPool.getConnection();
                statement = (WeaverStatement) connection.createStatement();
                ResultSet rs = statement.originExecuteQuery(sql);
                while (rs.next()) {
                    Integer id = rs.getInt("id");
                    if (id > REQUEST_ID_THRESHOLD) {
                        REQUEST_ID_THRESHOLD = id;
                    }
                }
                ILLEGAL_REQUEST_ID.removeAll(ILLEGAL_REQUEST_ID);
                ResultSet rs2 = statement.originExecuteQuery(illegalQuarySql);
                while (rs2.next()){
                    ILLEGAL_REQUEST_ID.add(rs2.getInt(1));
                }
            } catch (Exception e) {
                LOGGER.error("updateRequestIdThreshold STWorkflowInterceptor Error:", e);
            } finally {
                if (statement != null) {
                    statement.closeNoException();
                }
                if (connection != null) {
                    connectionPool.returnConnection(connection);
                }
            }
        }
        LOGGER.info("新REQUESTID阈值：" + REQUEST_ID_THRESHOLD);
        LOGGER.info("新时间阈值: " + TIME_THRESHOLD);
        LOGGER.info("==========");
    }

 private static void initThresholdData(){
        WeaverConnection connection = null;
        WeaverStatement statement = null;
        try {
            connection = ConnectionPool.getInstance().getConnection();
            statement = (WeaverStatement) connection.createStatement();
            //查询是否数据已存在
            String querySql = "";
            if (STConfig.DB_TYPE.equals(STDBSupport.SQLSERVER)) {
                querySql = "SELECT  count(1) FROM " + STDBConstants.DATA_THRESHOLD_TABLE_NAME + " WHERE module='workflow' ";
            } else if (STConfig.DB_TYPE.equals(STDBSupport.MYSQL)) {
                querySql = "SELECT count(1) FROM " + STDBConstants.DATA_THRESHOLD_TABLE_NAME + " WHERE module='workflow' ";
            } else if (STConfig.DB_TYPE.equals(STDBSupport.ORACLE)) {
                querySql = "SELECT  count(1) FROM " + STDBConstants.DATA_THRESHOLD_TABLE_NAME + " WHERE module='workflow' ";
            }
            ResultSet resultSet = statement.originExecuteQuery(querySql);
            resultSet.next();
            int count = resultSet.getInt(1);
            String initSql="";
            Date nowDate = new Date();
            DateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd");
            String nowDate_string = dFormat.format(nowDate);
            if (count>0){
                initSql = "update " + STDBConstants.DATA_THRESHOLD_TABLE_NAME + " set requestid_threshold="+REQUEST_ID_THRESHOLD+",createdate_threshold="+TIME_THRESHOLD+",updatetime="+nowDate_string+" WHERE module ='workflow' ";
            }else {
                initSql="insert into "+ STDBConstants.DATA_THRESHOLD_TABLE_NAME +" (requestid_threshold,createdate_threshold,module,updatetime) values("+REQUEST_ID_THRESHOLD+","+TIME_THRESHOLD+",'workflow',"+nowDate_string+")";
            }
            statement.originExecuteUpdate(initSql);
        }catch (Exception e){
            LOGGER.error("STWorkflowInterceptor initThresholdData error:", e);
        }finally {
            if (statement != null) {
                statement.closeNoException();
            }
            if (connection != null) {
                ConnectionPool.getInstance().returnConnection(connection);
            }
        }
    }


    private static String replaceSQL(String sql) {
        for (String tableName: STWorkflowConstants.SPLIT_TABLES.keySet()) {
            String tableNameRegex = "(?i)" + tableName;
            sql = sql.replaceAll(" " + tableNameRegex + " "," " + tableName + "_all ");
            sql = sql.replaceAll(" " + tableNameRegex + ","," " + tableName + "_all,");
            sql = sql.replaceAll("," + tableNameRegex + " ","," + tableName + "_all ");
            sql = sql.replaceAll("," + tableNameRegex + ",","," + tableName + "_all,");
            sql = sql.replaceAll(" " + tableNameRegex + "\\."," " + tableName + "_all.");
        }
        return sql;
    }
	
	public static void setRequestIdThreshold(Integer requestIdThreshold) {
        REQUEST_ID_THRESHOLD = requestIdThreshold;
    }

    public static void setTimeThreshold(String timeThreshold) {
        TIME_THRESHOLD = timeThreshold;
    }

    public static Integer getRequestIdThreshold() {
        return REQUEST_ID_THRESHOLD;
    }

    public static String getTimeThreshold() {
        return TIME_THRESHOLD;
    }
}
